package com.lerays.spider.sougou.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.lionsoul.websnail.util.TimeUtils;

import com.lerays.spider.sougou.utils.Utils;

/**
 * Mysql Dao class.
 * 
 * @author chenxin<chenxin619315@gmail.com>
 */
public class Dao 
{
	private static String driverUrl = "com.mysql.jdbc.Driver";
	
	private String host;
	private int port;
	private String db;
	
	private String user;
	private String pass;
	private boolean useUnicode = true;
	private String encoding = "UTF-8";
	
	private Connection conn = null;
	private int connectionCacheTime = 60 * 60 * 3;			//thats 3 hours
	private long lastConnectTime = 0L;
	
	public Dao( 
			String host,
			int port,
			String db, 
			String user, 
			String pass,
			boolean useUnicode,
			String encoding)  
	{
		this.host		= host;
		this.port		= port;
		this.db			= db;
		this.user		= user;
		this.pass		= pass;
		this.useUnicode	= useUnicode;
		this.encoding	= encoding;
		
		try {
			Class.forName(driverUrl);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public Dao(
			String host,
			int port,
			String db, 
			String user, 
			String pass)
	{
		this(host, port, db, user, pass, true, "utf-8");
	}
	
	/**
	 * get a mysql connection,  
	 * @throws SQLException 
	 */
	private Connection getConnection() throws SQLException
	{
		//check and reconnection the database server
		long now = TimeUtils.getCurrentTime();
		if ( conn == null
				|| (lastConnectTime + connectionCacheTime < now) )
		{
			//close the old connection
			close();
			
			StringBuilder sb = new StringBuilder();
			sb.append("jdbc:mysql://")
				.append(host).append(':')
				.append(port).append('/')
				.append(db)
				.append("?&useUnicode=")
				.append(useUnicode).append('&')
				.append("characterEncoding=")
				.append(encoding);
			
			conn = DriverManager.getConnection(sb.toString(), user, pass);
			lastConnectTime	= now;
			
			//System.out.println("Database Connectioned at " + 
			//	TimeUtils.timestampToDatetime(now, "yy-MM-dd HH:mm:ss"));
		}
		
		return conn;
	}
	
	public int getConnectionCacheTime() 
	{
		return connectionCacheTime;
	}

	public void setConnectionCacheTime(int connectionCacheTime) 
	{
		this.connectionCacheTime = connectionCacheTime;
	}
	
	public long getLastConnectTime() 
	{
		return lastConnectTime;
	}

	public void setLastConnectTime(long lastConnectTime) 
	{
		this.lastConnectTime = lastConnectTime;
	}

	public void close()
	{
		try {
			if (conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * batch insert to the leray_stream_spider
	 * 
	 * @param	data
	 * @throws SQLException 
	 */
	public int[] streamBatchInsert( 
			ArrayList<StreamBean> data ) throws SQLException
	{
		Statement stm = getConnection().createStatement();	
		StringBuilder sb = new StringBuilder();		//string builder
		sb.append("INSERT INTO ")
		.append(StreamBean.TBL_NAME).append('(')
		.append("cate_id,src_id,title,img_src,src_link,content,pubtime,scores,read_num")
		.append(") VALUES");
		
		Iterator<StreamBean> it = data.iterator();
		StreamBean item = null;
		boolean first = true;
		int count = 0;
		while ( it.hasNext() )
		{
			StringBuilder val = new StringBuilder();
			
			item = it.next();
			val.append("(")
				.append(item.getCateId())
				.append(',')
				.append(item.getSrcId())
				.append(",'")
				.append(Utils.getSqlString(item.getTitle()))
				.append("','")
				.append(item.getImgSrc())
				.append("','")
				.append(item.getSrcLink())
				.append("','0',")						//make the content default to 0
				.append(item.getPubtime())
				.append(',')
				.append(item.getScores())
				.append(',')
				.append(item.getReadNum())
			.append(")");
			
			if ( first ) first = false;
			else sb.append(',');			
			sb.append(val.toString());	
			count++;
		}
		
		stm.execute(sb.toString(),Statement.RETURN_GENERATED_KEYS);
		ResultSet rs = stm.getGeneratedKeys();
		//rs.getMetaData().getColumnCount();
		int id = 0;
		if (rs.next()) {    
	                id = rs.getInt(1);    
	                System.out.println ("生成记录的key为 ：" + id);    
	        }
		int re[] = new int[count];
		for (int i = 0; i < count; i++) {
		        re[i] = id+i;		      
		}
		stm.close();
		
		//System.out.println("#Debug: " + sb.toString());
		return re;
	}
	
	/**
	 * Load all the stream source
	 * 
	 * @param	where
	 * @param	order
	 * @param	limit
	 * @throws SQLException 
	 */
	public synchronized List<SourceBean> getAllSource( 
			String where, String order, String limit ) throws SQLException
	{
		Statement stm = getConnection()
				.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
				ResultSet.CONCUR_UPDATABLE);
		
		//sort the source by socres
		StringBuilder sb = new StringBuilder();
		sb.append("select Id,cate_id,_type,biz,scores from ").append(SourceBean.TBL_NAME);
		
		//check and append the where
		if ( where != null )
		{
			sb.append(" where ").append(where);
		}
		
		//check and append the order
		if ( order != null )
		{
			sb.append(" order by ").append(order);
		}
		
		//check and append the order
		if ( limit != null )
		{
			sb.append(" limit ").append(limit);
		}
		
		String sql = sb.toString();
		ResultSet ret = stm.executeQuery(sql);
		
		List<SourceBean> list = new ArrayList<SourceBean>();
		while ( ret.next() ) 
		{
			//add type limit
			int _type = ret.getInt("_type");
			if ( _type != 1 ) continue;
			
			list.add(SourceBean.create()
				.setId(ret.getInt("Id"))
				.setCateId(ret.getInt("cate_id"))
				.setBiz(ret.getString("biz"))
				.setScores(ret.getInt("scores"))
			);
		}
		
		//close the result set
		ret.close();
		stm.close();
		
		return list;
	}
}
